package com.example.dao;

import com.example.entity.SexEnum;
import com.example.entity.User;
import com.example.utils.JdbcUtils;

import java.sql.*;
import java.util.*;

/**
 * @author zhangs
 * @version v1.0
 * @decription 持久层实现
 * @date 2021/4/3 16:02
 **/
public class UserDaoImpl implements UserDao {
    private final Connection conn;

    private final static Map<String, String> sqlMap = new HashMap<>();
    // 预处理语句
    private final Map<String, PreparedStatement> psMap = new HashMap<>();

    static {
        // 初始化Sql语句
        String select = "select uid,username,age,sex from t_user ";
        String insert = "insert into t_user(username,age,sex) values ";

        sqlMap.put("queryById", select + "where uid = ?");
        sqlMap.put("queryLikeUsername", select + "where username like concat('%',?,'%')");
        sqlMap.put("queryLikeUsernameAndSex", select + "where username like concat('%',?,'%') and sex = ?");
        sqlMap.put("insertOne", insert + "(?,?,?)");
        sqlMap.put("last_insert_id", "select last_insert_id() as uid");
    }

    public UserDaoImpl(String path) throws Exception {
        // 获取连接对象
        conn = JdbcUtils.getConnection(path);
        conn.setAutoCommit(false);
    }

    private List<User> rs2list(ResultSet rs) throws SQLException {
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            // 遍历结果集
            int uid = rs.getInt("uid");
            String username = rs.getString("username");
            int age = rs.getInt("age");
            SexEnum sex = SexEnum.getSexEnumByCode(rs.getInt("sex"));

            // 将结果集保存到对象
            users.add(User.of()
                    .setUid(uid)
                    .setUsername(username)
                    .setAge(age)
                    .setSex(sex));
        }
        return users;
    }

    @Override
    public User queryById(int uid) throws SQLException {
        PreparedStatement queryById;
        // 获取预处理语句
        if (!psMap.containsKey("queryById")) {
            queryById = conn.prepareStatement(sqlMap.get("queryById"));
            psMap.put("queryById", queryById);
        } else {
            queryById = psMap.get("queryById");
        }
        // 绑定参数
        queryById.setInt(1, uid);

        // 执行查询
        ResultSet rs = queryById.executeQuery();
        List<User> list = rs2list(rs);
        return list.size() == 1 ? list.get(0) : null;
    }

    @Override
    public List<User> queryLikeUsername(String name) throws SQLException {
        PreparedStatement queryLikeUsername;
        // 获取预处理语句
        if (!psMap.containsKey("queryLikeUsername")) {
            queryLikeUsername = conn.prepareStatement(sqlMap.get("queryLikeUsername"));
            psMap.put("queryLikeUsername", queryLikeUsername);
        } else {
            queryLikeUsername = psMap.get("queryLikeUsername");
        }
        // 绑定参数
        queryLikeUsername.setString(1, name);
        // 执行查询
        ResultSet rs = queryLikeUsername.executeQuery();

        return rs2list(rs);
    }

    @Override
    public List<User> queryLikeUsernameAndSex(String name, int sex) throws SQLException {
        PreparedStatement queryLikeUsernameAndSex;
        // 获取预处理语句
        if (!psMap.containsKey("queryLikeUsernameAndSex")) {
            queryLikeUsernameAndSex = conn.prepareStatement(sqlMap.get("queryLikeUsernameAndSex"));
            psMap.put("queryLikeUsernameAndSex", queryLikeUsernameAndSex);
        } else {
            queryLikeUsernameAndSex = psMap.get("queryLikeUsernameAndSex");
        }
        // 绑定参数
        queryLikeUsernameAndSex.setString(1, name);
        queryLikeUsernameAndSex.setInt(2, sex);

        // 执行查询
        ResultSet rs = queryLikeUsernameAndSex.executeQuery();
        return rs2list(rs);
    }

    @Override
    public int insertOne(User user) throws SQLException {
        PreparedStatement insertOne;
        // 获取预处理语句
        if (!psMap.containsKey("insertOne")) {
            insertOne = conn.prepareStatement(sqlMap.get("insertOne"));
            psMap.put("insertOne", insertOne);
        } else {
            insertOne = psMap.get("insertOne");
        }

        // 绑定参数
        insertOne.setString(1, user.getUsername());
        insertOne.setInt(2, user.getAge());
        insertOne.setInt(3, user.getSex().getCode());

        // 执行查询
        int i = insertOne.executeUpdate();
        conn.commit(); // 提交事务

        // 执行查询，返回id
        PreparedStatement last_insert_id;
        if (!psMap.containsKey("last_insert_id")) {
            last_insert_id = conn.prepareStatement(sqlMap.get("last_insert_id"));
            psMap.put("last_insert_id", last_insert_id);
        } else {
            last_insert_id = psMap.get("last_insert_id");
        }

        // 主键回填
        ResultSet rs = last_insert_id.executeQuery();
        rs.next(); // 不可少
        int uid = rs.getInt("uid");
        user.setUid(uid);

        return i;
    }

    @Override
    public int[] insertMany(List<User> users) throws SQLException {
        PreparedStatement insertMany;
        // 获取预处理语句
        if (!psMap.containsKey("insertOne")) {
            insertMany = conn.prepareStatement(sqlMap.get("insertOne"));
            psMap.put("insertOne", insertMany);
        } else {
            insertMany = psMap.get("insertOne");
        }

        users.forEach(user -> {
            try {
                // 绑定参数
                insertMany.setString(1, user.getUsername());
                insertMany.setInt(2, user.getAge());
                insertMany.setInt(3, user.getSex().getCode());

                // 添加进批处理
                insertMany.addBatch();
            } catch (SQLException t) {
                t.printStackTrace();
            }
        });

        // 批量提交
        int[] count = insertMany.executeBatch();
        conn.commit(); // 提交事务
        return count;
    }

    /**
     * 构建sql更新语句
     *
     * @param user
     * @return
     */
    private String getSql(User user) {
        // 构建更新 sql
        StringBuilder sb = new StringBuilder();
        sb.append("update t_user set ");
        boolean nameExist = Objects.nonNull(user.getUsername());
        boolean ageExist = Objects.nonNull(user.getAge());
        boolean sexExist = Objects.nonNull(user.getSex());
        if (!nameExist && !ageExist && !sexExist) {
            return null;
        }

        if (nameExist) {
            sb.append("username = '").append(user.getUsername()).append("' and ");
        }

        if (ageExist) {
            sb.append("age = ").append(user.getAge()).append(" and ");
        }

        if (sexExist) {
            sb.append("sex = ").append(user.getSex().getCode()).append(" and ");
        }
        // 删除最后一个and
        sb.delete(sb.lastIndexOf("a") - 1, sb.capacity());
        sb.append(" where uid = ").append(user.getUid());
        return sb.toString();
    }

    @Override
    public int updateOne(User user) throws SQLException {
        String sql = getSql(user);

        if (sql == null) {
            return 0;
        }

        Statement stmt = conn.createStatement();

        int i = stmt.executeUpdate(sql); // 执行更新语句
        conn.commit(); // 提交事务
        return i;
    }

    @Override
    public int updateMany(List<User> users) throws SQLException {
        Statement stmt = conn.createStatement(); // 创建语句

        // 添加语句
        users.forEach(user -> {
            String sql = getSql(user);
            if (sql != null) {
                try {
                    stmt.addBatch(sql);
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });

        // 执行批量更新
        int[] ints = stmt.executeBatch();
        int cnt = 0;
        for (int anInt : ints) {
            if (anInt == 1) {
                cnt++;
            }
        }

        conn.commit(); // 提交事务
        return cnt;
    }

    @Override
    public int deleteOne(int uid) throws SQLException {
        // 删除数据
        Statement stmt = conn.createStatement();
        String sql = "delete from t_user where uid = " + uid;
        int i = stmt.executeUpdate(sql);
        conn.commit();
        return i;
    }

    @Override
    public int deleteMany(List<Integer> uids) throws SQLException {
        Statement stmt = conn.createStatement();
        String sql = "delete from t_user where uid = ";
        uids.forEach(i -> {
            try {
                stmt.addBatch(sql + i);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
        int[] ints = stmt.executeBatch();
        int cnt = 0;
        for (int anInt : ints) {
            if (anInt == 1) {
                cnt++;
            }
        }
        conn.commit(); // 提交事务
        return cnt;
    }

    @Override
    public void close() throws Exception {
        psMap.forEach((k, v) -> {
            JdbcUtils.close(v);
        });
        JdbcUtils.close(conn);
    }
}
